import pandas as pd
import xml.etree.ElementTree as ET
from xml.dom import minidom


class ExcelToXMLConverter:
    def __init__(self, excel_file):
        self.excel_file = excel_file

    def safe_int_convert(self, value, default=0):
        """安全地将各种格式的数值转换为整数"""
        if pd.isna(value) or value == '':
            return default
        try:
            # 先尝试直接转换整数
            return int(value)
        except (ValueError, TypeError):
            try:
                # 如果直接转换失败，先转float再转int
                return int(float(value))
            except (ValueError, TypeError):
                return default

    def convert_to_xml(self, output_file):
        """从Excel生成XML文件"""
        # 读取Excel数据
        test_cases_df = pd.read_excel(self.excel_file, sheet_name='TestCase')
        test_steps_df = pd.read_excel(self.excel_file, sheet_name='TestSteps')
        messages_df = pd.read_excel(self.excel_file, sheet_name='Messages')

        # 处理数值列，确保是整数格式
        if 'Delay' in test_steps_df.columns:
            test_steps_df['Delay'] = test_steps_df['Delay'].apply(lambda x: self.safe_int_convert(x, 0))
        if 'Timeout' in test_steps_df.columns:
            test_steps_df['Timeout'] = test_steps_df['Timeout'].apply(lambda x: self.safe_int_convert(x, 1000))
        if 'StepID' in test_steps_df.columns:
            test_steps_df['StepID'] = test_steps_df['StepID'].apply(lambda x: self.safe_int_convert(x, 0))

        # 处理消息定义的数值列
        numeric_columns = ['CAN_ID', 'CAN_Type', 'TransmitType', 'FrameCount']
        for col in numeric_columns:
            if col in messages_df.columns:
                messages_df[col] = messages_df[col].apply(lambda x: self.safe_int_convert(x, 0))

        # 创建XML根元素
        root = ET.Element("CANTestSequence")

        # 添加测试场景
        for _, test_case in test_cases_df.iterrows():
            if test_case['Enabled'] == 'Yes':
                scenario = ET.SubElement(root, "TestScenario")
                scenario.set("name", test_case['TestCaseName'])

                # 添加描述
                desc = ET.SubElement(scenario, "Description")
                desc.text = str(test_case['Description'])

                # 添加测试步骤
                steps = test_steps_df[test_steps_df['TestCaseID'] == test_case['TestCaseID']]
                # 按StepID排序
                steps = steps.sort_values('StepID')

                for _, step in steps.iterrows():
                    step_elem = ET.SubElement(scenario, "Step")
                    step_elem.set("sequence", str(step['StepID']))

                    ET.SubElement(step_elem, "Action").text = step['Action']

                    if pd.notna(step['MessageName']):
                        ET.SubElement(step_elem, "Message").set("name", step['MessageName'])

                    if pd.notna(step['ExpectedMessage']):
                        ET.SubElement(step_elem, "ExpectedMessage").set("name", step['ExpectedMessage'])

                    if pd.notna(step['Delay']):
                        # 确保输出整数格式
                        delay_value = self.safe_int_convert(step['Delay'], 0)
                        ET.SubElement(step_elem, "Delay").text = str(delay_value)

                    if pd.notna(step['Timeout']):
                        # 确保输出整数格式
                        timeout_value = self.safe_int_convert(step['Timeout'], 1000)
                        ET.SubElement(step_elem, "Timeout").text = str(timeout_value)

        # 添加消息定义
        msg_defs = ET.SubElement(root, "MessageDefinitions")
        for _, msg in messages_df.iterrows():
            msg_elem = ET.SubElement(msg_defs, "Message")
            msg_elem.set("name", msg['MessageName'])

            # 确保所有数值字段都是整数格式
            ET.SubElement(msg_elem, "can_id").text = str(self.safe_int_convert(msg['CAN_ID'], 0))
            ET.SubElement(msg_elem, "can_type").text = str(self.safe_int_convert(msg['CAN_Type'], 0))
            ET.SubElement(msg_elem, "data_array").text = msg['DataArray']
            ET.SubElement(msg_elem, "transmit_type").text = str(self.safe_int_convert(msg['TransmitType'], 0))
            ET.SubElement(msg_elem, "frame_count").text = str(self.safe_int_convert(msg['FrameCount'], 1))
            ET.SubElement(msg_elem, "is_canfd").text = str(msg['IsCANFD']).lower()
            ET.SubElement(msg_elem, "is_remote").text = str(msg['IsRemote']).lower()

        # 美化并保存XML
        xml_str = minidom.parseString(ET.tostring(root)).toprettyxml(indent="  ")
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write(xml_str)

        print(f"XML文件已生成: {output_file}")
        return True


# 使用示例
if __name__ == "__main__":
    converter = ExcelToXMLConverter("TestCaseTemplate.xlsx")
    success = converter.convert_to_xml("auto_generated_test_cases.xml")
    if success:
        print("转换成功！")
    else:
        print("转换失败")